package com.base.jdbc.template;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.base.jdbc.connect.MysqlDbFactory;
import com.base.jdbc.page.Page;

public class JdbcTemplate {
	
	/**
	 * 通过sql查询单条Map
	 * 
	 * @param sql
	 * @throws SQLException
	 */
	public Map<String, Object> findOneForMap(String sql) {
		Map<String, Object> map = new HashMap<String, Object>();
		PreparedStatement stat = null ;
		ResultSet rs = null ;
		try {
			 stat = MysqlDbFactory.getStatment(sql);
			 rs = stat.executeQuery();
			ResultSetMetaData rData = rs.getMetaData();
			while (rs.next()) {
				for (int i = 1; i <= rData.getColumnCount(); i++) {
					map.put(rData.getColumnName(i).toLowerCase(), rs.getObject(i));
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			MysqlDbFactory.close(stat, rs);
		}
		return map;
	}
	
	/**
	 * 通过sql查询map 预编译
	 * @param sql
	 * @return
	 */
	public Map<String, Object> findOneForMap(String sql,Object[] params) {
		Map<String, Object> map = new HashMap<String, Object>();
		PreparedStatement stat = null ;
		ResultSet rs = null ;
		try {
			 stat = MysqlDbFactory.getStatment(sql);
			 if(params!=null && params.length>0){
				 for(int i=1;i<=params.length;i++){
					 stat.setObject(i, params[i]);
				 }
			 }
			 rs = stat.executeQuery();
			ResultSetMetaData rData = rs.getMetaData();
			while (rs.next()) {
				for (int i = 1; i <= rData.getColumnCount(); i++) {
					map.put(rData.getColumnName(i).toLowerCase(), rs.getObject(i));
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			MysqlDbFactory.close(stat, rs);
		}
		return map;
	}
	
	/**
	 * 数量
	 * @param sql
	 * @param params
	 * @return
	 */
	public Long findCountNumbers(String sql,Object[] params) {
		String rowSql = "select count(1) numbers from ( " + sql +") t";
		Map<String, Object> map =  findOneForMap(rowSql,params);
		Long count = (Long) map.get("numbers");
		return count;
	}
	/**
	 * 通过sql查询List
	 * 
	 * @param sql
	 * @throws SQLException
	 */
	public List<Map<String, Object>> findForList(String sql){
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		PreparedStatement stat = null ;
		ResultSet rs = null ;
		try {
			 stat = MysqlDbFactory.getStatment(sql);
			 rs = stat.executeQuery();
			ResultSetMetaData rData = rs.getMetaData();
			while (rs.next()) {
				Map<String, Object> map = new HashMap<String, Object>();
				for (int i = 1; i <= rData.getColumnCount(); i++) {
					map.put(rData.getColumnName(i).toLowerCase(), rs.getObject(i));
				}
				list.add(map);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			MysqlDbFactory.close(stat, rs);
		}
		return list;
	}
	
	
	/**
	 * 通过sql查询map 预编译
	 * @param sql
	 * @return
	 */
	public List<Map<String, Object>> findForList(String sql,Object[] params) {
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		PreparedStatement stat = null ;
		ResultSet rs = null ;
		try {
			 stat = MysqlDbFactory.getStatment(sql);
			 if(params!=null && params.length>0){
				 for(int i=1;i<=params.length;i++){
					 stat.setObject(i, params[i]);
				 }
			 }
			 rs = stat.executeQuery();
			ResultSetMetaData rData = rs.getMetaData();
			while (rs.next()) {
				Map<String, Object> map = new HashMap<String, Object>();
				for (int i = 1; i <= rData.getColumnCount(); i++) {
					map.put(rData.getColumnName(i).toLowerCase(), rs.getObject(i));
				}
				list.add(map);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			MysqlDbFactory.close(stat, rs);
		}
		return list;
	}
	
	
	/**
	 * 返回页面
	 * @return
	 */
	public Page findForPage(String sql,Object[] params,int pageNo,int pageSize){
		String dataSql = "select * from ( " + sql +") jdbcpagesearch limit " + (pageNo-1)*pageSize +","+pageNo*pageSize;
		Long pageTotalRows = findCountNumbers(sql, params);
		List<Map<String, Object>> data = findForList(dataSql, params);
		Page page = new Page();
		page.setData(data);
		page.setPageNo(pageNo);
		page.setPageSize(pageSize);
		page.setPageTotalRows(pageTotalRows);
		return page;
	}
}
